package com.wxtf.platform.main.contraceptives;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.AccessDataBaseUtil;
import com.wxtf.platform.tools.Common;
import com.wxtf.platform.tools.ConstantInfoHelper;
/**
 * <li>Title: LowerLevelAction.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.contraceptives</li>
 * <li>Description: 查看下级库存</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Jul 5, 2012 10:08:48 AM</li>
 *
 * @author liu_zhaojun
 * @version 1.0.0.0
 *
 */
public class LowerLevelAction extends LogicBase implements
		CommonsCallProvider {
	
	private static Log log = LogFactory.getLog(LowerLevelAction.class);
	
	public LowerLevelAction(String jsonArray) {

	}
	
	@Override
	public Object delete(Object jsonArray) throws Exception {
		return null;
	}
	
	@Override
	public Object insert(Object jsonArray) throws Exception {		
		return null;
	}

	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Object run() {
		Object msg = "";
		try {
			if ("queryPage".equals(this.getMethodName())) {// 列表
				msg = this.select(this.getLogicData());
			}else if ("selectTmaStoreCheckByTmascmName".equals(this.getMethodName())) {// 列表
				msg = this.selectTmaStoreCheckByTmascmName(this.getLogicData());
			}else if ("selectSubOrgId".equals(this.getMethodName())) {// 列表
				msg = this.selectSubOrgId(this.getLogicData());
			}else if ("expData".equals(this.getMethodName())) {// 列表
				msg = this.expData(this.getLogicData());
			} else {
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}
			if(msg == null || "[null]".equals(msg)){
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch (Exception e) {
			msg = "操作失败，请联系管理员";
			msg = ConstantInfoHelper.generateJSONString(ConstantInfoHelper.JSON_HEAD, ConstantInfoHelper.getFailResultDetail(msg.toString()), false);
			log.error(e, e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}
	
	/** 
	 * 描述：selectSubOrgId 查询下属机构
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Jul 10, 2012  5:12:42 PM
	 * @author liu_zhaojun
	 */
	public Object selectSubOrgId(Object jsonArray) throws Exception {
		UserBean user = (UserBean)(this.getRequest()).getSession().getAttribute("userBean");
		String org_id = user.getCurOrgId();
		StringBuffer buff = new StringBuffer("select department_id,caption from t_department");
		buff.append(" where dparent_id='").append(org_id).append("'");
		String[] fields = {"department_id","caption"};
		List<Map<String, String>> list = AccessDataBaseUtil.query(fields, buff.toString(), null);
		JSONArray array = JSONArray.fromObject(list);
		return array.toString();
	}
	
	/** 
	 * 描述：selectTmaStoreCheckByTmascmName 查询盘点名称
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Jul 10, 2012  5:12:42 PM
	 * @author liu_zhaojun
	 */
	public Object selectTmaStoreCheckByTmascmName(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String org_id = jsonObject.getString("org_id");
		StringBuffer buff = new StringBuffer(" from tma_store_check_main");
		buff.append(" where org_id='").append(org_id).append("'");
		String querySql = "select tmascm_id,tmascm_name" + buff.toString();
		String[] fields = {"tmascm_id","tmascm_name"};
		List<Map<String, String>> list = AccessDataBaseUtil.query(fields, querySql, null);
		array = JSONArray.fromObject(list);
		return array.toString();
	}
	
	/** 
	 * 描述：select 查询盘点记录
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Jul 10, 2012  5:12:42 PM
	 * @author liu_zhaojun
	 */
	@Override
	public Object select(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String org_id = jsonObject.getString("org_id");
		String tmascm_id = jsonObject.getString("tmascm_id");
		StringBuffer buff = new StringBuffer("select b.tmgd_name,a.tmad_name,a.tmad_company_name,a.tmad_spec,a.tmad_price,a.tmad_batch_code,ij.back_tmascd_num,ij.tmaid_num,ij.tmaod_num,(ij.tmaid_num-ij.tmaod_num) as tmascd_num,ij.tmascd_num_y,ij.tmascd_num_s,ij.tmascd_num_c,ij.tmascd_num as num,ij.tmascd_num*a.tmad_price as money"); 
		buff.append(" from tma_dictionary a inner join tm_general_dictionary b on a.tmad_kind=b.tmgd_id and a.reserve1='1' and a.tmad_status='0' and b.tmgd_type='105'") 
		.append(" inner join (select i.tmad_id,i.back_tmascd_num,i.tmaid_num,i.tmaod_num,i.tmascd_num_y,i.tmascd_num_s,i.tmascd_num_c,i.tmascd_num from tma_store_check_detail i inner join tma_store_check_main j on i.tmascm_id=j.tmascm_id and i.tmascm_id='").append(tmascm_id).append("' and org_id='").append(org_id).append("') ij on ij.tmad_id=a.tmad_id") 
		.append(" group by a.tmad_id order by b.tmgd_name,a.tmad_name");
		String[] fields = {"tmgd_name","tmad_name","tmad_company_name","tmad_spec","tmad_price","tmad_batch_code","back_tmascd_num","tmaid_num","tmaod_num","tmascd_num","tmascd_num_y","tmascd_num_s","tmascd_num_c","num","money"};
		List<Map<String, String>> list = AccessDataBaseUtil.query(fields, buff.toString(), null);
		array = JSONArray.fromObject(list);
		return "{\"Rows\":"+array.toString()+"}";
	}
	
	/** 
	 * 描述：expData 导出
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Sep 5, 2012  5:06:00 PM
	 * @author liu_zhaojun
	 */
	public Object expData(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String org_id = jsonObject.getString("org_id");
		String tmascm_id = jsonObject.getString("tmascm_id");
		StringBuffer buff = new StringBuffer("select a.tmad_name,a.tmad_company_name,a.tmad_spec,a.tmad_price,a.tmad_batch_code,ij.back_tmascd_num,ij.tmaid_num,ij.tmaod_num,(ij.tmaid_num-ij.tmaod_num) as tmascd_num,ij.tmascd_num_c,ij.tmascd_num_y,ij.tmascd_num_s,ij.tmascd_num as num,ij.tmascd_num*a.tmad_price as money"); 
		buff.append(" from tma_dictionary a inner join tm_general_dictionary b on a.tmad_kind=b.tmgd_id and a.reserve1='1' and a.tmad_status='0' and b.tmgd_type='105'") 
		.append(" inner join (select i.tmad_id,i.back_tmascd_num,i.tmaid_num,i.tmaod_num,i.tmascd_num_y,i.tmascd_num_s,i.tmascd_num_c,i.tmascd_num from tma_store_check_detail i inner join tma_store_check_main j on i.tmascm_id=j.tmascm_id and i.tmascm_id='").append(tmascm_id).append("' and org_id='").append(org_id).append("') ij on ij.tmad_id=a.tmad_id") 
		.append(" group by a.tmad_id order by a.tmad_name");
		List<Object[]> list = AccessDataBaseUtil.query(buff.toString(), null);		
		String url = this.getRequest().getSession().getServletContext().getRealPath("/jsp/app/xls/");
		String model = url + "/model/lowerLevelManager.xls";
		String file = url + "/file/lowerLevelManager.xls";
		String rfile = "jsp/app/xls/file/lowerLevelManager.xls";
		File ffrom = new File(model);
		File fto = new File(file);
		Common.copyFile(ffrom, fto);
		if(list.size() > 0){
			writeXls(list, fto, "药具盘查", 3);//写xls内容
		} else {
			return "{\"result\":false,\"resultmessage\":\"\",\"resultrecord\":\"导出结果集没有记录\"}";
		}
		
		return "\"" + rfile + "\"";
	}
	
	/** 
	 * 描述：writeXls 写XLS文件内容
	 * @param list 数据集
	 * @param file 写入文件
	 * @param title sheet页标题
	 * @param startRow xls数据起始行
	 * @throws Exception
	 * @CreateOn Sep 7, 2012  9:25:24 AM
	 * @author liu_zhaojun
	 */
	public static void writeXls(List<Object[]> list, File file, String title, int startRow) throws Exception {
		POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(file));
		HSSFWorkbook book = new HSSFWorkbook(filein);
		HSSFSheet sheet = book.getSheet(title);
		int index = 0;
		double sum = 0.00;
		if(null != list && list.size() > 0){
			index = list.size();
			HSSFRow row = null;
			for (int i = 0; i < list.size(); i++) {// 行
				row = sheet.createRow(i + startRow);
				row.setHeight((short) 400);
				
				//序号
				HSSFCell cell = row.createCell(0);
				cell.setCellValue((i+1) + "");
				
				Object[] metaData = list.get(i);
				for(int j = 0; j < metaData.length; j++) {
					cell = row.createCell(j + 1);
					// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
					String textValue = null;
					if(j==8){
						textValue = Double.valueOf(metaData[j].toString()).intValue()+"";
					}else if(j==13){
						textValue = Double.valueOf(metaData[j].toString())*100/100+"";
						sum += Double.valueOf(textValue);
					}else{
						textValue = metaData[j].toString();
					}
					cell.setCellValue(new HSSFRichTextString(textValue));
				}
				
			}
		}
		index = index + startRow;
		HSSFRow row = null;
		HSSFCell cell = null;
		row = sheet.createRow(index);
		row.setHeight((short) 400);
		cell = row.createCell(13);
		// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
		cell.setCellValue(new HSSFRichTextString("总金额："));
		cell = row.createCell(14);
		// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
		cell.setCellValue(new HSSFRichTextString(sum*100/100+""));		
		FileOutputStream fileOut = new FileOutputStream(file);
		book.write(fileOut);
		fileOut.close();
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}
	
	@Override
	public Object update(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		// TODO Auto-generated method stub
		return null;
	}

}
